import pymysql
import requests
import json
from jsonpath import jsonpath

conn = pymysql.connect(host="localhost", user="root", password="admin", database="fp", charset="utf8")
cursor = conn.cursor()

headers={
'Cookie':'LOCALE_LANG=zh-cn; i18n=zh; ARK_ID=JS5b4e4f7aa22173864655fa8e1423d1ba5b4e',
'Referer':'https://qianfan.analysys.cn/refine/view/rankApp/rankApp.html',
'User-Agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:73.0) Gecko/20100101 Firefox/73.0'
}

url='https://qianfan.analysys.cn/refine/qianfan/topRank/listAllTopRank?statDate=1577808000000&page=1&pageSize=100&sort=&sortField=&rootCateId=1'
response = requests.get(url, headers=headers)

json_str = '{"content":'+response.text+'}'
unicodestr = json.loads(json_str)

appName = jsonpath(unicodestr,'$..appName')    #app
tradeName = jsonpath(unicodestr,'$..tradeName')  #所属行业
developCompanyFullName = jsonpath(unicodestr,'$..developCompanyFullName')  #开发商
rank = jsonpath(unicodestr,'$..rank')  #排名
cateId = jsonpath(unicodestr,'$..cateId')  #环比增幅
activeNums = jsonpath(unicodestr,'$..activeNums')  #人数
print(appName)

i = 0
for flag in appName:
    print(appName[i])
    sql = "insert into liveness(appname, industry, developers, Indexrank, Mindex, Dindex)" \
            " VALUES ('%s','%s','%s','%s','%s','%s')"%\
            (appName[i], tradeName[i],  developCompanyFullName[i],  rank[i],  cateId[i],  activeNums[i])
    i+=1
    try:
                # 执行sql语句
        cursor.execute(sql)
                # 提交到数据库执行
        conn.commit()
    except Exception as e:
            print(e)
                # 如果发生错误则回滚
            conn.rollback()















